package org.example.pack1210.dao;

// bean+DAO   EmpDao ❌

import lombok.SneakyThrows;
import org.example.pack1210.bean.Emp;
import org.example.pack1210.db.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class EmpDAO {

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    @SneakyThrows
    // save add insert 将传递过来的数据存放到数据库对应表中就行
    public int save(Emp emp) {
        conn = DBManager.getConnection();
        String sql = "insert into emp values(null,?,?,?,now(),?,?,?,1)";
        ps = conn.prepareStatement(sql);
        ps.setString(1, (String) emp.getEnname());
        ps.setString(2, emp.getJob());
        ps.setInt(3, emp.getMgr());
        ps.setDouble(4, emp.getSal());
        ps.setDouble(5, emp.getComm());
        ps.setInt(6, emp.getDeptNo());
        return ps.executeUpdate();
    }

    @SneakyThrows
    // delete remove
    public int delete(int id) {
        conn = DBManager.getConnection();
        String sql = "update emp set state=0 where empno=?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        return ps.executeUpdate();
    }

    @SneakyThrows
    // 修改操作
    public int update(Emp emp) {
        conn = DBManager.getConnection();
        String sql = "update emp set enname=?,job=?,mgr=?,sal=?,comm=?,deptno=? where empno=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, emp.getEnname());
        ps.setString(2, emp.getJob());
        ps.setInt(3, emp.getMgr());
        ps.setDouble(4, emp.getSal());
        ps.setDouble(5, emp.getComm());
        ps.setInt(6, emp.getDeptNo());
        ps.setInt(7, emp.getEmpNo());
        int row = ps.executeUpdate();
        DBManager.closeConnection(conn, ps);
        return row;
    }

    // find
    @SneakyThrows
    public Emp findById(int id) {
        conn = DBManager.getConnection();
        String sql = "select * from emp where state=1 and empno=" + id;
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        if (rs.next()) {
            Emp emp = new Emp();
            int empNo = rs.getInt(1);
            String enname = rs.getString(2);
            String job = rs.getString("job");
            int mgr = rs.getInt(4);
            String hireDate = rs.getString("hireDate");
            double sal = rs.getDouble("sal");
            double comm = rs.getDouble("comm");
            int deptNo = rs.getInt("deptno");
            int state = rs.getInt(9);
            // 封装
            emp.setEmpNo(empNo);
            emp.setComm(comm);
            emp.setJob(job);
            emp.setEnname(enname);
            emp.setHiredate(hireDate);
            emp.setDeptNo(deptNo);
            emp.setMgr(mgr);
            emp.setState(state);
            emp.setSal(sal);
            return emp;
        }
        return null;
    }

    @SneakyThrows
    public List<Emp> findAll() {
        List<Emp> list = new ArrayList<>();
        conn = DBManager.getConnection();
        String sql = "select * from emp where state=1";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            Emp emp = new Emp();
            int empNo = rs.getInt(1);
            String enname = rs.getString(2);
            String job = rs.getString("job");
            int mgr = rs.getInt(4);
            String hireDate = rs.getString("hireDate");
            double sal = rs.getDouble("sal");
            double comm = rs.getDouble("comm");
            int deptNo = rs.getInt("deptno");
            int state = rs.getInt(9);
            // 封装
            emp.setEmpNo(empNo);
            emp.setComm(comm);
            emp.setJob(job);
            emp.setEnname(enname);
            emp.setHiredate(hireDate);
            emp.setDeptNo(deptNo);
            emp.setMgr(mgr);
            emp.setState(state);
            emp.setSal(sal);
            list.add(emp);
        }
        return list;
    }

    @SneakyThrows
    public List<Emp> findByEname(String name) {
        List<Emp> list = new ArrayList<>();
        conn = DBManager.getConnection();
        String sql = "select * from emp where state=1 and enname like ?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, "%" + name + "%");
        rs = ps.executeQuery();
        while (rs.next()) {
            Emp emp = new Emp();
            int empNo = rs.getInt(1);
            String enname = rs.getString(2);
            String job = rs.getString("job");
            int mgr = rs.getInt(4);
            String hireDate = rs.getString("hireDate");
            double sal = rs.getDouble("sal");
            double comm = rs.getDouble("comm");
            int deptNo = rs.getInt("deptno");
            int state = rs.getInt(9);
            // 封装
            emp.setEmpNo(empNo);
            emp.setComm(comm);
            emp.setJob(job);
            emp.setEnname(enname);
            emp.setHiredate(hireDate);
            emp.setDeptNo(deptNo);
            emp.setMgr(mgr);
            emp.setState(state);
            emp.setSal(sal);
            list.add(emp);
        }
        return list;
    }

    @SneakyThrows
    public List<Emp> findByPage(int page) {
        List<Emp> list = new ArrayList<>();
        conn = DBManager.getConnection();
        String sql = "select * from emp where state=1 limit ?,10";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, (page - 1) * 10);
        rs = ps.executeQuery();
        while (rs.next()) {
            Emp emp = new Emp();
            int empNo = rs.getInt(1);
            String enname = rs.getString(2);
            String job = rs.getString("job");
            int mgr = rs.getInt(4);
            String hireDate = rs.getString("hireDate");
            double sal = rs.getDouble("sal");
            double comm = rs.getDouble("comm");
            int deptNo = rs.getInt("deptno");
            int state = rs.getInt(9);
            // 封装
            emp.setEmpNo(empNo);
            emp.setComm(comm);
            emp.setJob(job);
            emp.setEnname(enname);
            emp.setHiredate(hireDate);
            emp.setDeptNo(deptNo);
            emp.setMgr(mgr);
            emp.setState(state);
            emp.setSal(sal);
            list.add(emp);
        }
        return list;
    }
}

